﻿/*
 * Copyright 2009 - 2010 eScience Institute
 *
 * This file is part of SqlShare.
 *
 * SqlShare is free software: you can redistribute it and/or modify it under the terms 
 * of the GNU General Public License as published by the Free Software Foundation, 
 * either version 3 of the License, or (at your option) any later version.
 *
 * SqlShare is distributed in the hope that it will be useful, but WITHOUT ANY 
 * WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR 
 * A PARTICULAR PURPOSE. See the GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License along with 
 * SqlShare. If not, see http://www.gnu.org/licenses/.
*/
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using System.Data;
using System.Data.SqlClient;
using Westwind.Web;
using Westwind.Web.Controls;
using Westwind.Utilities;
using Westwind.Web.JsonSerializers;
using Microsoft.WindowsAzure.ServiceRuntime;
using SqlShareAPI;

namespace WebQueryRole
{
    public partial class _Default : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {

            
            Proxy.ClientProxyTargetType = typeof(AJAXCallbackHandler);          
            // reset anything that should be blank on a new run
            divErrors.InnerHtml = "";

            // load up everything we should see everytime we load the page
            // javascript vars, listbox values, etc
            // note: dont catch error if we are in dev environemnt, allow VS to catch/block
            if (!RoleEnvironment.IsAvailable)
            {
                LoadPageVars();
            }
            else
            {
                try { LoadPageVars(); }
                catch (Exception ex) { ReportErrorToHTML(ex.Message); }
            }

            // if are loading this page due to a submit button, determine which one was pressed            
            if (!String.IsNullOrEmpty(sqlTextBox.Value))
            {
                int maxRecords = 0; // unlimited
                int.TryParse(limitSelect.SelectedValue,out maxRecords);
                HandleSQLQuery(sqlTextBox.Value, maxRecords);
            }
            
        }

        /// <summary>
        /// this function manages how to add errors (if multiple) to the error report div inside the Default.aspx page
        /// </summary>
        /// <param name="msg"></param>
        private void ReportErrorToHTML(string msg)
        {
            divErrors.InnerHtml += "<p style=\"color:red;\">" + msg.Replace("\n", "<br />") + "</p>";    
        }

       
        private void LoadPageVars()
        {
            List<UserQuery> queries = (List<UserQuery>)Session[sqlshare_master.USER_QUERIES];

            UserSession session = (UserSession)Session[sqlshare_master.USER_SESSION];

            querySelectList.Items.Clear();
            foreach (UserQuery query in queries)
            {
                string label = query.short_desc;
                if (!query.owner.Equals(session.UserName))
                {
                    label += " (" + query.owner + ")";
                }
                // since the short desc name is unique, we can use it as a value
                querySelectList.Items.Add(new ListItem(label, query.short_desc));
            }           

            if (!String.IsNullOrEmpty(Request["sql_code"]))
            {
                sqlTextBox.Value = Request["sql_code"];
            }
        }

        private void SkinDataGrid(DataGrid dg)
        {
            dg.CssClass = "sql_result";
            dg.AlternatingItemStyle.CssClass = "row_odd";
            dg.ItemStyle.CssClass = "row_even";
            dg.HeaderStyle.CssClass = "row_header";
        }

        private void HandleSQLQuery(string query, int maxRecords)
        {            
            // for now assume its always a select query
            UserSession userSession = (UserSession)Session[sqlshare_master.USER_SESSION];
            SqlCommand cmd = new SqlCommand(query, userSession.ContentSqlConnection);
            cmd.CommandTimeout = 300;
            int recordsFound = 0;
            
            try
            {
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                if(maxRecords > 0) {
                    recordsFound = adapter.Fill(ds, 0, maxRecords, "source");
                }
                else {
                    recordsFound = adapter.Fill(ds);
                }
                
                DataGrid dg = new DataGrid();
                dg.DataSource = ds;
                dg.DataBind();
                SkinDataGrid(dg);
                myPlaceHolder.Controls.Add(dg);

                divSqlRowsFound.InnerHtml = "Your query generated " + recordsFound + " result(s)";
            }
            catch (Exception e)
            {
                ReportErrorToHTML(e.Message);
            }
        }

        
        /*
        

        // ajax calls
        [CallbackMethod]
        public string ajaxSaveQuery(string shortDesc, string longDesc, string sqlCode, bool isPublic)
        {
            CleanProxyStr(ref shortDesc);
            CleanProxyStr(ref longDesc);
            CleanProxyStr(ref sqlCode);

            if (shortDesc.Equals(""))
            {
                throw new Exception("invalid short description to update");
            }

            UserSession session = WebAuthentication.IsLoggedIn(this, _factory);
            if (session == null)
            {
                return "";
            }
            UserQuery query = session.SaveUserQuery(shortDesc, longDesc, sqlCode, isPublic);
            JSONSerializer ser = new JSONSerializer();
            return ser.Serialize(query);
        }

        /// <summary>
        /// The ajax library we use seems to want to substitute "null" when the initial string is empty ""
        /// </summary>
        /// <param name="str"></param>
        private void CleanProxyStr(ref string str)
        {
            if (str.Equals("null"))
            {
                str = "";
            }
        }

        // ajax calls
        [CallbackMethod]
        public string ajaxSaveTable(string tablename, string desc, bool isPublic)
        {

            CleanProxyStr(ref desc);

            UserSession session = WebAuthentication.IsLoggedIn(this, _factory);
            if (session == null)
            {
                return "";
            }
            UserTable table = session.SaveUserTable(tablename, desc, isPublic);
            JSONSerializer ser = new JSONSerializer();
            return ser.Serialize(table);
        }

        [CallbackMethod]
        public string ajaxDeleteQuery(string shortDesc)
        {
            try
            {
                UserSession session = WebAuthentication.IsLoggedIn(this, _factory);
                if (session == null)
                {
                    return "";
                }
                session.DeleteUserQuery(shortDesc);
                return "true";
            }
            catch (Exception e)
            {
                Console.WriteLine("Exception (ajaxDeleteSqlVar): " + e.Message);
                return "false";
            }
        }
         */
    }         
}
